﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace DAL
{
    public class DataHelper
    {
        public DataHelper()
        {
        }

        #region Campos

        private SqlConnection _Con;
        public SqlCommand _Cmd = new SqlCommand();
        private SqlTransaction _tran;

        #endregion

        #region Métodos Auxiliares

        public void OpenConnection(string conexao)
        {
            _Con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["Conexao"].ConnectionString.ToString());
            _Con.Open();
        }
        public void CloseConnection()
        {
            _Con.Close();
        }

        public void AddParameter(string param, object conteudo)
        {
            _Cmd.Parameters.AddWithValue(param, conteudo);
        }

        public void StartTransaction()
        {
            _tran = _Con.BeginTransaction();
            _Cmd.Transaction = _tran;
        }

        public void CloseTransaction()
        {
            _tran.Commit();
        }

        public void RollBackTransaction()
        {
            _tran.Rollback();
        }

        #endregion

        #region Métodos

        public SqlDataReader ExecuteReader(string SP)
        {
            _Cmd.CommandText = SP;
            _Cmd.CommandType = CommandType.StoredProcedure;
            _Cmd.Connection = _Con;
            _Cmd.CommandTimeout = 10000;
            SqlDataReader dr = null;

            try
            {
                dr = _Cmd.ExecuteReader();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dr;
        }

        public void ExecuteNonQuery(string SP)
        {
            _Cmd.CommandText = SP;
            _Cmd.CommandType = CommandType.StoredProcedure;
            _Cmd.Connection = _Con;
            try
            {
                if (_tran == null) { _Con.Open(); }
                else { _Cmd.Transaction = _tran; }
                _Cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public string ExecuteScalar(string SP)
        {
            _Cmd.CommandText = SP;
            _Cmd.CommandType = CommandType.StoredProcedure;

            _Cmd.Connection = _Con;
            try
            {
                if (_tran == null) { _Con.Open(); }
                else { _Cmd.Transaction = _tran; }
                return _Cmd.ExecuteScalar().ToString();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_tran == null) _Con.Close();
            }
        }

        #endregion
    }
}
